package db.tables.reference;

import java.sql.SQLException;
import java.util.ArrayList;

import org.hsqldb.Types;

import db.DBMain;
import db.DBObject;
import db.tables.DBCategoryTable;
import db.tables.DBUserTable;

public class DBContactTable extends DBObject {
	public static final String TABLE_NAME="contact";
	
	public static final String CONTACT_ID_COL = TABLE_NAME+"_id",
			USER_ID_COL = DBUserTable.USER_ID_COL, 
			CONTACT_USER_ID_COL = "contact_user_id",
			CATEGORY_ID_COL = DBCategoryTable.CATEGORY_ID_COL, 
			START_DATE_COL = "start_date", END_DATE_COL = "end_date";

	public DBContactTable() throws ClassNotFoundException, SQLException {
		super(TABLE_NAME, new Object[][] {
				{ CONTACT_ID_COL, Types.BIGINT, SQL_IDENTITY_PRIMARY_KEY, "" },
				{ USER_ID_COL, Types.BIGINT, "", "" },
				{ CONTACT_USER_ID_COL, Types.BIGINT, "",""},
				{ CATEGORY_ID_COL, Types.BIGINT, "", "" },
				{ START_DATE_COL, Types.DATE, SQL_NOT_NULL, "" },
				{ END_DATE_COL, Types.DATE,
					getSQLForeignKeyReference(DBCategoryTable.TABLE_NAME, CATEGORY_ID_COL)
					+getSQLForeignKeyReference(DBUserTable.TABLE_NAME, USER_ID_COL)
					+getSQLForeignKeyReference(DBUserTable.TABLE_NAME, CONTACT_USER_ID_COL, 
						DBUserTable.USER_ID_COL), "" }});
	}
	
	public boolean areUserFriends(Long user_id, Long user_contact_id){
		return countValues(new String[]{CONTACT_USER_ID_COL,CONTACT_USER_ID_COL,USER_ID_COL,USER_ID_COL}, 
				new Object[]{user_id,user_contact_id,user_id,user_contact_id})==1;
	}
	
	public int getRelationshipStatus(Long user_id, Long user_contact_id){
		Long cat=(Long)selectObject(CATEGORY_ID_COL, new String[]{USER_ID_COL,CONTACT_USER_ID_COL}, new Object[]{user_id,user_contact_id})[0];
		return DBMain.getCategoryTable().selectInt(DBCategoryTable.CATEGORY_ORDER_COL, DBCategoryTable.CATEGORY_ID_COL, cat);
	}
	
	public ArrayList<ArrayList<Object>> getArrayListByUserID(Long user_id){
		return getReferenceTableJoin(tableName, DBUserTable.TABLE_NAME, 
				getAbsoluteColumn(CONTACT_USER_ID_COL), 
				getAbsoluteColumn(DBUserTable.TABLE_NAME, DBUserTable.USER_ID_COL), 
				getAbsoluteColumn(USER_ID_COL),user_id);
	}
}
